package com.lovo.sgproj.dao.imp;

import com.lovo.sgproj.bean.RecordBean;
import com.lovo.sgproj.dao.RecordDAO;
import com.lovo.sgproj.util.DBUtil;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

@Component
public class RecordDAOImp implements RecordDAO {
    @Override
    public ArrayList<RecordBean> findAllRecordByRoomID(int roomId) {
        ArrayList<RecordBean> recordLst = new ArrayList<>();
        String sql = "select pk_recordid,f_description,f_reportdate,f_solution,fk_roomid from t_record where fk_roomid=?";
        try (Connection con = DBUtil.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setInt(1, roomId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                RecordBean record = new RecordBean();
                record.setRecordID(rs.getInt("pk_recordid"));
                record.setDescription(rs.getString("f_description"));
                record.setRecordDate(rs.getDate("f_reportdate"));
                record.setSolution(rs.getBoolean("f_solution"));


                recordLst.add(record);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return recordLst;
    }

    @Override
    public void insertRecord(RecordBean record, int roomID) {
        String sql1 = "insert into t_record (f_description,f_reportdate,f_solution,fk_roomid) values(?,?,0,?)";
        String sql2 = "update t_room set f_roomstatus = 0 where pk_roomid = ?";
        try (Connection con = DBUtil.getConnection()) {
            con.setAutoCommit(false);
            try (PreparedStatement ps1 = con.prepareStatement(sql1);
                 PreparedStatement ps2 = con.prepareStatement(sql2)) {
                ps1.setString(1, record.getDescription());
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM--dd");
                java.sql.Date timePara = null;
                try {
                    timePara = new java.sql.Date(record.getRecordDate().getTime());

                    ps1.setDate(2, timePara);
                    ps1.setInt(3, roomID);
                    ps1.executeUpdate();

                    ps2.setInt(1, roomID);
                    ps2.executeUpdate();

                    con.commit();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            } catch (Exception e) {
                con.rollback();
                e.printStackTrace();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void updateRecord(int recordID, int roomID) {
        String sql1 = "update t_record set f_solution = 1 where pk_recordid = ?";
        String sql2 = "select count(*) from t_record where f_solution=0 and fk_roomid = ?";
        String sql3 = "update t_room set f_roomstatus = 1 where pk_roomID = ?";
        try (Connection con = DBUtil.getConnection()) {
            con.setAutoCommit(false);
            try (PreparedStatement ps1 = con.prepareStatement(sql1);
                 PreparedStatement ps2 = con.prepareStatement(sql2);
                 PreparedStatement ps3 = con.prepareStatement(sql3)) {
                ps1.setInt(1, recordID);
                ps1.executeUpdate();
                ps2.setInt(1, roomID);
                ResultSet rs = ps2.executeQuery();
                while (rs.next()) {
                    int count = rs.getInt("count(*)");
                    if (count == 0) {
                        ps3.setInt(1, roomID);
                        ps3.executeUpdate();
                    }
                }
                con.commit();
            } catch (SQLException e) {
                con.rollback();
                e.printStackTrace();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}
